﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Backend;
using System.Data.SqlClient;

namespace PersistenceTier
{
    public class TicketPersistence
    {
        SqlConnection conn;

        public TicketPersistence()
        {
            conn = DBConnection.getConnection().getSQLConnection();

        }


        public bool Book(Ticket ticket)
        {
            conn.Open();

            
            SqlTransaction trans;
            trans = conn.BeginTransaction();


            bool tickedSaved = SaveTicket(ticket, trans);
            bool ticketLegsConnected = ConnectTicketLeg(ticket, trans);
            bool passengersSaved = SavePassengers(ticket, trans);


            if (tickedSaved && ticketLegsConnected && passengersSaved)
            {


                trans.Commit();
                conn.Close();
                return true;
            }



            else
            {
                conn.Close();
                return false; 
            }
          


        }

        //Associate the ticket with the legs(flightlegs)
        public bool ConnectTicketLeg(Ticket ticket,SqlTransaction trans)
        {

            try
            {
                foreach (Leg leg in ticket.Flight.Legs)
                {


                    String Query = "use dm77_1 insert into [Flight-Ticket] values(" + leg.Id + ",'" + ticket.TicketReff + "')";

                    SqlCommand myCommand = new SqlCommand(Query, conn, trans);
                    Console.WriteLine(Query);
                    SqlDataReader reader = myCommand.ExecuteReader();
                    reader.Close();

                    Query = "use dm77_1 update Flight Set nrSeats=(nrSeats-" + ticket.Passengers.Count + ") where  id=" + leg.Id;
                    Console.WriteLine(Query);
                    myCommand = new SqlCommand(Query, conn, trans);
                    reader = myCommand.ExecuteReader();

                    reader.Close();

                   


                }
                return true;
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong between the legs");
                Console.WriteLine(ex.Message);
                trans.Rollback();
                return false;

            }

        
        }

        private bool SaveTicket(Ticket ticket, SqlTransaction trans)
        {
            try
            {
                String Query = "use dm77_1 insert into Ticket values('" + ticket.TicketReff + "', " + ticket.TotalPrice + ")";
                Console.WriteLine(Query);
                SqlCommand myCommand = new SqlCommand(Query, conn, trans);
                SqlDataReader reader = myCommand.ExecuteReader();
                reader.Close();
                return true;
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with the ticket");
                Console.WriteLine(ex.Message);
                trans.Rollback();
                return false;

            }


        
        }

        private bool SavePassengers(Ticket ticket, SqlTransaction trans)
        {
            try
            {
                foreach (Passenger pass in ticket.Passengers)
                {


                    String Query = "use dm77_1 insert into Passenger values (" + pass.DbID + " , '" + pass.FName + "', '" + pass.LName + "', '" + pass.Title + "','" + pass.DateOfBirth + "', '" + pass.Gender + "', '" + pass.Contacts + "' , '" + ticket.TicketReff + "')";
                    Console.WriteLine(Query);
                    SqlCommand myCommand = new SqlCommand(Query, conn, trans);
                    SqlDataReader reader = myCommand.ExecuteReader();
                    reader.Close();
                }

                return true;
            }


            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with the passengers");
                Console.WriteLine(ex.Message);
                trans.Rollback();
                return false;

            }
        
        }
 
    }
}
